﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace StuMana.Services
{
    class student
    {
        private ConnectionController connection = new ConnectionController();
        private MySqlCommand cmd;
        public int TagID = -1;
        public string Name = "";
        public string Birthday = "";
        public string Gender = "";

        public string Class = "";
        public int Account = -1;
        public string LateTime = "";
        public Boolean InOut = false;
        public DateTime time;
        List<string>[] list;
        private string[] columnName;

        // close Connect
        public Boolean CloseConnect()
        {
            return connection.CloseConnection();

        }
        // Add new student to studentinfo
        public Boolean AddNewStudent()
        {
            try
            {
                //sau khi đã check thẻ, create thẻ xong thì ghi xuống database.
                string query = "INSERT INTO studentinfo (TagID, Name, BirthDay, Gender, Class, Account,LateTime,InOut_status) VALUES(@a1, @a2, @a3, @a4, @a5, @a6,@a7,@a8)";
               cmd = new MySqlCommand(query, connection.connection);
                cmd.Parameters.AddWithValue("@a1", TagID);
                cmd.Parameters.AddWithValue("@a2", Name);
                cmd.Parameters.AddWithValue("@a3", Birthday);
                cmd.Parameters.AddWithValue("@a4", Gender);
                cmd.Parameters.AddWithValue("@a5", Class);
                cmd.Parameters.AddWithValue("@a6", Account);
                cmd.Parameters.AddWithValue("@a7", LateTime);
                cmd.Parameters.AddWithValue("@a8", InOut);

                if (connection.executeNonQuery(cmd))
                    return true;
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n"+ es.Message);
                return false;
            }
        }

        // Add new student to studentinfo
        public Boolean AddNewActivity()
        {
            time = DateTime.Now;
            try
            {
                //sau khi đã check thẻ, create thẻ xong thì ghi xuống database.
                string query =  "INSERT INTO `time_inout` (`TagID`, `Time`, `InOut`) VALUES(@a1, @a2, @a3)";
                cmd = new MySqlCommand(query, connection.connection);

                cmd.Parameters.AddWithValue("@a1", TagID);
                cmd.Parameters.AddWithValue("@a2", time.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@a3", !InOut);


                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }
        

        // GetInFo to display to checkin_out
        public Boolean getStdInfo(string tag)
        {
            try
            {
                //sau khi đã check thẻ, create thẻ xong thì ghi xuống database.
                string query = "SELECT * FROM studentinfo WHERE TagID="+tag;

                columnName = new string[] { "TagID", "Name", "BirthDay", "Gender", "Class", "Account", "InOut_status" };
                MySqlCommand cmd2 = new MySqlCommand(query, connection.getMySqlConnection());
                list = connection.selectStringType(cmd2, columnName, 2);
                connection.CloseConnection();
                
                if (list != null)
                {
                    TagID = int.Parse(tag);
                    Name = list[1][0];
                    Gender = list[3][0];
                    Class = list[4][0];
                    InOut = bool.Parse(list[6][0]);
                    return true;
                }
                else 
                    return false;                
            }
            catch (Exception es)
            {

                MessageBox.Show("Không tìm thấy trong cơ sở dữ liệu.\n"+ es.Message, "Database Error");
                return false;
            }
        }

        // Change status of InOut
        public Boolean change_stt(string tag)
        {
            string query;
            try
            {
                //sau khi đã check thẻ, create thẻ xong thì ghi xuống database.
                
                    query = "UPDATE studentinfo SET InOut_status=@a1 WHERE TagID="+tag;
                
                cmd = new MySqlCommand(query, connection.connection);
                if (InOut)
                    cmd.Parameters.AddWithValue("@a1", false);
                else
                    cmd.Parameters.AddWithValue("@a1", true);

                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }

        // delete row
        public Boolean delete_account(string tagid)
        {
            string query;
            try
            {
                //sau khi đã check thẻ, create thẻ xong thì ghi xuống database.

                query = "Delete FROM studentinfo WHERE TagID ='" + tagid + "'";

                cmd = new MySqlCommand(query, connection.connection);

                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else
                    return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }

        //update
        public Boolean update_account(string tagid)
        {
            time = DateTime.Now;
            try
            {
                //sau khi đã check thẻ, create thẻ xong thì ghi xuống database.
                string query = "UPDATE studentinfo SET Name=@a1, BirthDay=@a2, Gender=@a3, Class=@a4, LateTime=@a5, Account=@a6 WHERE TagID=@a7";
                cmd = new MySqlCommand(query, connection.connection);

                cmd.Parameters.AddWithValue("@a1", Name);
                cmd.Parameters.AddWithValue("@a2", Birthday);
                cmd.Parameters.AddWithValue("@a3", Gender);
                cmd.Parameters.AddWithValue("@a4", Class);
                cmd.Parameters.AddWithValue("@a5", LateTime);
                cmd.Parameters.AddWithValue("@a6", Account);
                cmd.Parameters.AddWithValue("@a7", tagid);
               

                if (connection.executeNonQuery(cmd))
                {
                    cmd = null;
                    return true;
                }
                else return false;
            }
            catch (Exception es)
            {
                MessageBox.Show("Loi ket noi co so du lieu \n" + es.Message);
                return false;
            }
        }
    }
}
